package fire.and.near.challenger.database;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import fire.and.near.challenger.R;
import fire.and.near.challenger.database.TableConstants.ChallengesTable;
import fire.and.near.challenger.database.TableConstants.FriendsTable;
import fire.and.near.challenger.database.crypto.CryptoUtils;
import fire.and.near.challenger.database.limit.ChallengeConstraint;

public class ChallengeDatabaseHelper extends SQLiteOpenHelper {

	private SQLiteDatabase database = null;
	private CryptoUtils crypto = null;
	private Context context = null;
	private final static String DATABASE_NAME = "Challenges.db";
	private static final int DATABASE_VERSION = 10;

	public ChallengeDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
		this.database = this.getWritableDatabase();
		this.crypto = new CryptoUtils();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i("DbHelper onCreate", "hi");

		db.execSQL("Create table Challenges(ID integer primary key autoincrement,Description varchar(800),Answer varchar(800),Hashtag varchar(100),ChallengeType tinyint,PhotoPath varchar(100),ChallengerID bigint,ChallengedID bigint,Accepted NUMERIC,Status NUMERIC);");
		db.execSQL(this.context.getResources().getString(
				R.string.create_table_friends));

		SharedPreferences sharedPrefs = ((Activity) this.context)
				.getSharedPreferences(ChallengeConstraint.FILENAME,
						Context.MODE_PRIVATE);
		SharedPreferences.Editor editor = sharedPrefs.edit();
		editor.putLong(DefaultChallengesManager.LAST_DEFAULT_CHALLENGE_ID, 0);
		editor.commit();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(this.context.getResources().getString(
				R.string.drop_table_challenges));
		db.execSQL(this.context.getResources().getString(
				R.string.drop_table_friends));
		this.onCreate(db);
	}

	public long insertChallenges(List<ChallengeModel> challenges) {

		long rowID = -1;

		for (ChallengeModel challenge : challenges) {
			/* Insert into Challenges Table */

			ContentValues challengeValues = new ContentValues();

			try {
				challengeValues.put(ChallengesTable.DESCRIPTION,
						crypto.encrypt(challenge.getDescription()));
				challengeValues.put(ChallengesTable.ANSWER,
						crypto.encrypt(challenge.getAnswer()));
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			challengeValues
					.put(ChallengesTable.HASHTAG, challenge.getHashtag());
			challengeValues.put(ChallengesTable.CHALLENGE_TYPE,
					challenge.getChallengeTypeForDatabase());
			challengeValues.put(ChallengesTable.PHOTO_PATH,
					challenge.getPhotoPath());
			challengeValues.put(ChallengesTable.CHALLENGER_ID, challenge
					.getChallenger().getFbID());
			challengeValues.put(ChallengesTable.CHALLENGED_ID,
					challenge.getChallengedID());
			challengeValues.put(ChallengesTable.ACCEPTED,
					challenge.isAccepted());
			challengeValues.put(ChallengesTable.STATUS, challenge.getStatus()
					.asInt());
			/* insert into Friends table */
			ContentValues friendValues = new ContentValues();
			friendValues.put(FriendsTable.FB_ID, challenge.getChallenger()
					.getFbID());
			friendValues.put(FriendsTable.NAME, challenge.getChallenger()
					.getName());

			if ((rowID = this.database.insert(ChallengesTable.TABLE_NAME, null,
					challengeValues)) <= 0
					|| this.database.insertWithOnConflict(
							FriendsTable.TABLE_NAME, null, friendValues,
							SQLiteDatabase.CONFLICT_REPLACE) < 0) {
				rowID = -1;
			}
		}

		return rowID;
	}

	public ChallengeModel getDefaultChallenge() {
		Cursor c = this.database.query(ChallengesTable.TABLE_NAME,
				new String[] { ChallengesTable.ID, ChallengesTable.DESCRIPTION,
						ChallengesTable.HASHTAG,
						ChallengesTable.CHALLENGE_TYPE,
						ChallengesTable.CHALLENGED_ID,
						ChallengesTable.CHALLENGER_ID }, ChallengesTable.STATUS
						+ " = " + ChallengeStatus.IN_DATABASE.asInt(), null,
				null, null, null);
		if (c.getCount() <= 0) {
			return null;
		}
		// choose random challenge
		Random rand = new Random();
		int position = rand.nextInt(c.getCount());
		c.moveToPosition(position);
		ChallengeModel challenge = new ChallengeModel();
		challenge.setID(c.getInt(c.getColumnIndex(ChallengesTable.ID)));
		try {
			challenge.setDescription(crypto.decrypt(c.getString(c
					.getColumnIndex(ChallengesTable.DESCRIPTION))));
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		challenge.setHashtag(c.getString(c
				.getColumnIndex(ChallengesTable.HASHTAG)));
		challenge.setChallengeType(c.getInt(c
				.getColumnIndex(ChallengesTable.CHALLENGE_TYPE)));
		challenge.setChallengedID(c.getLong(c
				.getColumnIndex(ChallengesTable.CHALLENGED_ID)));
		challenge.setChallengerID(new FriendModel(c.getLong(c
				.getColumnIndex(ChallengesTable.CHALLENGER_ID))));

		// mark default challenge
		ContentValues values = new ContentValues();
		values.put(ChallengesTable.STATUS,
				ChallengeStatus.IN_MY_CHALLENGES.asInt());
		this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ? ",
				new String[] { Integer.toString(challenge.getID()) });

		return challenge;
	}

	public boolean declineChallenge(int challengeID) {
		return complete(challengeID, false);
	}

	public boolean completeChallenge(int challengeID) {
		return complete(challengeID, true);
	}

	private boolean complete(int challengeID, boolean success) {
		ContentValues values = new ContentValues();
		values.put(ChallengesTable.ACCEPTED, success ? 1 : 0); // accept :
																// decline
		values.put(ChallengesTable.STATUS, ChallengeStatus.IN_HISTORY.asInt()); // add
																				// to
																				// history

		if (this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ? ",
				new String[] { Integer.toString(challengeID) }) > 0) {
			return true;
		}
		return false;
	}

	public List<ChallengeModel> getMyChallenges(long challengedID) {
		Cursor c = this.database.rawQuery("SELECT C." + ChallengesTable.ID
				+ ", C." + ChallengesTable.DESCRIPTION + ", C."
				+ ChallengesTable.ANSWER + ", C." + ChallengesTable.HASHTAG
				+ ", C." + ChallengesTable.CHALLENGE_TYPE + ", C."
				+ ChallengesTable.CHALLENGER_ID + ", F." + FriendsTable.NAME
				+ " FROM " + ChallengesTable.TABLE_NAME + " AS C JOIN "
				+ FriendsTable.TABLE_NAME + " AS F ON C."
				+ ChallengesTable.CHALLENGER_ID + " = F." + FriendsTable.FB_ID
				+ " WHERE C." + ChallengesTable.STATUS + " = "
				+ ChallengeStatus.IN_MY_CHALLENGES.asInt() + " AND C."
				+ ChallengesTable.CHALLENGED_ID + " = ?;",
				new String[] { Long.toString(challengedID) });

		ArrayList<ChallengeModel> challenges = new ArrayList<ChallengeModel>();
		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++, c.moveToNext()) {
			ChallengeModel ch = new ChallengeModel();
			ch.setID(c.getInt(c.getColumnIndex(ChallengesTable.ID)));
			try {
				ch.setDescription(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.DESCRIPTION))));
				ch.setAnswer(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.ANSWER))));
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			ch.setHashtag(c.getString(c.getColumnIndex(ChallengesTable.HASHTAG)));
			ch.setChallengeType(c.getInt(c
					.getColumnIndex(ChallengesTable.CHALLENGE_TYPE)));
			ch.getChallenger()
					.setFbID(
							(c.getLong(c
									.getColumnIndex(ChallengesTable.CHALLENGER_ID))));
			ch.setChallengedID(challengedID);
			ch.setAccepted(true);
			ch.setStatus(ChallengeStatus.IN_MY_CHALLENGES);
			ch.getChallenger().setFbID(
					c.getLong(c.getColumnIndex(ChallengesTable.CHALLENGER_ID)));
			ch.getChallenger().setName(
					c.getString(c.getColumnIndex(FriendsTable.NAME)));
			challenges.add(ch);
		}

		return challenges;
	}

	public List<ChallengeModel> getPendingForFacebook(long challengedID) {
		Cursor c = this.database.rawQuery("SELECT C." + ChallengesTable.ID
				+ ", C." + ChallengesTable.DESCRIPTION + ", C."
				+ ChallengesTable.ANSWER + ", C." + ChallengesTable.HASHTAG
				+ ", C." + ChallengesTable.CHALLENGE_TYPE + ", C."
				+ ChallengesTable.CHALLENGER_ID + ", C."
				+ ChallengesTable.PHOTO_PATH + ", C."
				+ ChallengesTable.ACCEPTED + ", F." + FriendsTable.NAME
				+ " FROM " + ChallengesTable.TABLE_NAME + " AS C JOIN "
				+ FriendsTable.TABLE_NAME + " AS F ON C."
				+ ChallengesTable.CHALLENGER_ID + " = F." + FriendsTable.FB_ID
				+ " WHERE (C." + ChallengesTable.STATUS + " = "
				+ ChallengeStatus.PENDING_FOR_FACEBOOK.asInt() + ") AND C."
				+ ChallengesTable.CHALLENGED_ID + " = ?;",
				new String[] { Long.toString(challengedID) });

		ArrayList<ChallengeModel> challenges = new ArrayList<ChallengeModel>();
		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++, c.moveToNext()) {
			ChallengeModel ch = new ChallengeModel();
			ch.setID(c.getInt(c.getColumnIndex(ChallengesTable.ID)));
			try {
				ch.setDescription(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.DESCRIPTION))));
				ch.setAnswer(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.ANSWER))));
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			ch.setHashtag(c.getString(c.getColumnIndex(ChallengesTable.HASHTAG)));
			ch.setChallengeType(c.getInt(c
					.getColumnIndex(ChallengesTable.CHALLENGE_TYPE)));
			ch.getChallenger().setFbID(
					c.getLong(c.getColumnIndex(ChallengesTable.CHALLENGER_ID)));
			ch.setPhotoPath(c.getString(c
					.getColumnIndex(ChallengesTable.PHOTO_PATH)));
			ch.setAccepted(c.getInt(c.getColumnIndex(ChallengesTable.ACCEPTED)) == 1 ? true
					: false);
			ch.setStatus(ChallengeStatus.IN_HISTORY);
			ch.setChallengedID(challengedID);
			ch.getChallenger().setFbID(
					c.getLong(c.getColumnIndex(ChallengesTable.CHALLENGER_ID)));
			ch.getChallenger().setName(
					c.getString(c.getColumnIndex(FriendsTable.NAME)));
			challenges.add(ch);
		}

		if (challenges.size() > 0) {
			return challenges;
		} else {
			return null;
		}
	}

	public List<ChallengeModel> getHistoryChallenges(long challengedID) {
		Cursor c = this.database.rawQuery("SELECT C." + ChallengesTable.ID
				+ ", C." + ChallengesTable.DESCRIPTION + ", C."
				+ ChallengesTable.ANSWER + ", C." + ChallengesTable.HASHTAG
				+ ", C." + ChallengesTable.CHALLENGE_TYPE + ", C."
				+ ChallengesTable.CHALLENGER_ID + ", C."
				+ ChallengesTable.PHOTO_PATH + ", C."
				+ ChallengesTable.ACCEPTED + ", F." + FriendsTable.NAME
				+ " FROM " + ChallengesTable.TABLE_NAME + " AS C JOIN "
				+ FriendsTable.TABLE_NAME + " AS F ON C."
				+ ChallengesTable.CHALLENGER_ID + " = F." + FriendsTable.FB_ID
				+ " WHERE (C." + ChallengesTable.STATUS + " = "
				+ ChallengeStatus.IN_HISTORY.asInt() + " OR C."
				+ ChallengesTable.STATUS + " = "
				+ ChallengeStatus.PENDING_FOR_FACEBOOK.asInt() + ") AND C."
				+ ChallengesTable.CHALLENGED_ID + " = ?;",
				new String[] { Long.toString(challengedID) });

		ArrayList<ChallengeModel> challenges = new ArrayList<ChallengeModel>();
		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++, c.moveToNext()) {
			ChallengeModel ch = new ChallengeModel();
			ch.setID(c.getInt(c.getColumnIndex(ChallengesTable.ID)));
			try {
				ch.setDescription(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.DESCRIPTION))));
				ch.setAnswer(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.ANSWER))));
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			ch.setHashtag(c.getString(c.getColumnIndex(ChallengesTable.HASHTAG)));
			ch.setChallengeType(c.getInt(c
					.getColumnIndex(ChallengesTable.CHALLENGE_TYPE)));
			ch.getChallenger().setFbID(
					c.getLong(c.getColumnIndex(ChallengesTable.CHALLENGER_ID)));
			ch.setPhotoPath(c.getString(c
					.getColumnIndex(ChallengesTable.PHOTO_PATH)));
			ch.setAccepted(c.getInt(c.getColumnIndex(ChallengesTable.ACCEPTED)) == 1 ? true
					: false);
			ch.setStatus(ChallengeStatus.IN_HISTORY);
			ch.setChallengedID(challengedID);
			ch.getChallenger().setFbID(
					c.getLong(c.getColumnIndex(ChallengesTable.CHALLENGER_ID)));
			ch.getChallenger().setName(
					c.getString(c.getColumnIndex(FriendsTable.NAME)));
			challenges.add(ch);
		}

		if (challenges.size() > 0) {
			return challenges;
		} else {
			return null;
		}
	}

	public List<ChallengeModel> getPendingChallenges(long challengerID) {
		Cursor c = this.database
				.query(ChallengesTable.TABLE_NAME,
						new String[] { ChallengesTable.ID,
								ChallengesTable.DESCRIPTION,
								ChallengesTable.ANSWER,
								ChallengesTable.HASHTAG,
								ChallengesTable.CHALLENGE_TYPE,
								ChallengesTable.PHOTO_PATH,
								ChallengesTable.CHALLENGED_ID,
								ChallengesTable.ACCEPTED },
						ChallengesTable.STATUS + " = "
								+ ChallengeStatus.PENDING_FOR_SERVER.asInt()
								+ " AND " + ChallengesTable.CHALLENGER_ID
								+ " = ?",
						new String[] { Long.toString(challengerID) }, null,
						null, null);

		ArrayList<ChallengeModel> challenges = new ArrayList<ChallengeModel>();
		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++, c.moveToNext()) {
			ChallengeModel ch = new ChallengeModel();
			ch.setID(c.getInt(c.getColumnIndex(ChallengesTable.ID)));
			try {
				ch.setDescription(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.DESCRIPTION))));
				ch.setAnswer(crypto.decrypt(c.getString(c
						.getColumnIndex(ChallengesTable.ANSWER))));
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			ch.setHashtag(c.getString(c.getColumnIndex(ChallengesTable.HASHTAG)));
			ch.setChallengeType(c.getInt(c
					.getColumnIndex(ChallengesTable.CHALLENGE_TYPE)));
			ch.getChallenger().setFbID(challengerID);
			ch.setChallengedID(c.getLong(c
					.getColumnIndex(ChallengesTable.CHALLENGED_ID)));
			ch.setPhotoPath(c.getString(c
					.getColumnIndex(ChallengesTable.PHOTO_PATH)));
			ch.setAccepted(c.getInt(c.getColumnIndex(ChallengesTable.ACCEPTED)) == 1 ? true
					: false);
			ch.setChallengedID(c.getLong(c
					.getColumnIndex(ChallengesTable.CHALLENGED_ID)));
			challenges.add(ch);
		}

		if (challenges.size() > 0) {
			return challenges;
		} else {
			return null;
		}
	}

	public boolean updatePending(long challengeID, boolean pending) {
		ContentValues values = new ContentValues();
		values.put(ChallengesTable.STATUS,
				pending ? ChallengeStatus.PENDING_FOR_SERVER.asInt()
						: ChallengeStatus.IN_HISTORY.asInt());

		if (this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ?",
				new String[] { Long.toString(challengeID) }) > 0) {
			return true;
		} else {
			return false;
		}

	}

	public boolean deletePendingChallenges(long challengerID) {
		if (this.database.delete(ChallengesTable.TABLE_NAME,
				ChallengesTable.STATUS + " = "
						+ ChallengeStatus.PENDING_FOR_SERVER.asInt() + " AND "
						+ ChallengesTable.CHALLENGER_ID + " = ?",
				new String[] { Long.toString(challengerID) }) > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean deleteLastPending(long challengerID) {
		Cursor c = this.database.query(ChallengesTable.TABLE_NAME,
				new String[] { ChallengesTable.ID }, ChallengesTable.STATUS
						+ " = " + ChallengeStatus.PENDING_FOR_SERVER.asInt()
						+ " AND " + ChallengesTable.CHALLENGER_ID + " = ?",
				new String[] { Long.toString(challengerID) }, null, null,
				ChallengesTable.ID + " DESC", "1");
		c.moveToLast();
		if (this.database.delete(ChallengesTable.TABLE_NAME, ChallengesTable.ID
				+ " = " + c.getInt(c.getColumnIndex(ChallengesTable.ID)), null) > 0) {
			return true;
		} else {
			return false;
		}
	}

	public List<FriendModel> getFriendsList() {
		List<FriendModel> friends = null;
		Cursor c = this.database.rawQuery("SELECT * FROM "
				+ FriendsTable.TABLE_NAME + ";", null);
		if (c.getCount() > 0) {

			c.moveToFirst();
			for (int i = 0; i < c.getCount(); i++, c.moveToNext()) {

			}
		}

		return friends;
	}

	public boolean deleteChallenge(Integer challengeID) {
		if (this.database.delete(ChallengesTable.TABLE_NAME, ChallengesTable.ID
				+ " = ?", new String[] { challengeID.toString() }) > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean updatePhotoPath(Integer challengeId, String newPhotoPath) {
		ContentValues values = new ContentValues();
		values.put(ChallengesTable.PHOTO_PATH, newPhotoPath);

		return this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ?",
				new String[] { challengeId.toString() }) > 0;

	}

	public boolean updateAnswer(Integer challengeId, String answer) {
		ContentValues values = new ContentValues();
		try {
			values.put(ChallengesTable.ANSWER, crypto.encrypt(answer));
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		Log.i("ZMIQTA WE HELP", "" + challengeId + " : " + answer);

		return this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ?",
				new String[] { challengeId.toString() }) > 0;

	}

	public boolean setChallengeStatus(Integer challengeID,
			ChallengeStatus status) {
		ContentValues values = new ContentValues();
		values.put(ChallengesTable.STATUS, String.valueOf(status.asInt()));

		if (status == ChallengeStatus.PENDING_FOR_FACEBOOK) {
			values.put(ChallengesTable.ACCEPTED, 1);
		}

		return this.database.update(ChallengesTable.TABLE_NAME, values,
				ChallengesTable.ID + " = ?",
				new String[] { challengeID.toString() }) > 0;
	}
}